const express = require('express');
const bodyParser = require('body-parser');
const app = express();

// ----------------------------
var mysql  = require('mysql'); 
// function sql (sql) {

// } 
// var Result = []
// 数据库查询函数
 var selectSql = function (sql) {
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : 'root',       
        port: '3306',                   
        database: 'studentmanger' 
      }); 
    connection.connect();
    return new Promise((res,req) => {
        connection.query(sql,function (err, result) {
            if(err){
              console.log('[SELECT ERROR] - ',err.message);
              return;
            }
            res(result)
        });
        connection.end();
    })
    
    // console.log("sql",sqlResult);
    // return sqlResult
 }

// --------------------------------
// 数据库插入函数
var addSql = function (sql) {
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : 'root',       
        port: '3306',                   
        database: 'studentmanger' 
      }); 
    connection.connect();
    return new Promise((res,reject) => {
        connection.query(sql,function (err, result) {
            if(err){
              console.log('[SELECT ERROR] - ',err.message);
              reject(err.message)
            //   return;
            }
            res(result)
        });
        connection.end();
    })
 }
 //成绩录入
 var scoreSql = function (sql) {
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : 'root',       
        port: '3306',                   
        database: 'studentmanger' 
      }); 
    connection.connect();
    return new Promise((res,reject) => {
        connection.query(sql,function (err, result) {
            if(err){
              console.log('[SELECT ERROR] - ',err.message);
              reject(err.message)
            //   return;
            }
            res(result)
        });
        connection.end();
    })
 }
 var addSql = function (sql) {
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : 'root',       
        port: '3306',                   
        database: 'studentmanger' 
      }); 
    connection.connect();
    return new Promise((res,reject) => {
        connection.query(sql,function (err, result) {
            if(err){
              console.log('[SELECT ERROR] - ',err.message);
              reject(err.message)
            //   return;
            }
            res(result)
        });
        connection.end();
    })
 }
 //成绩录入
 var scoreSql = function (sql) {
    var connection = mysql.createConnection({     
        host     : 'localhost',       
        user     : 'root',              
        password : 'root',       
        port: '3306',                   
        database: 'studentmanger' 
      }); 
    connection.connect();
    return new Promise((res,reject) => {
        connection.query(sql,function (err, result) {
            if(err){
              console.log('[SELECT ERROR] - ',err.message);
              reject(err.message)
            //   return;
            }
            res(result)
        });
        connection.end();
    })
 }
// --------------------------------

app.all('*', function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header('Access-Control-Allow-Headers', 'Content-Type, Content-Length, Authorization, Accept, X-Requested-With , yourHeaderFeild');
    res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS");
    res.header("X-Powered-By",' 3.2.1')
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
});
// -------------------------------------
app.get('/login',(req,res) => {
    selectSql('SELECT * FROM user')
    .then((data) => {
        res.send(JSON.stringify(data));
    })
})
app.get('/select',(req,res) => {
    console.log(req.query);
    let sql ='select s.sno,s.sname,s.sex,s.age,s.dept,sc.cno,sc.grade,sc.credit,c.cname from s,sc,c where s.sno=sc.sno AND sc.cno=c.cno'
    if(req.query.sno!=''){
        sql = sql +` AND s.sno='${req.query.sno}'`
    }
    if(req.query.sname!=''){
        sql = sql +` AND s.sname='${req.query.sname}'`
    }
    if(req.query.dept!=''){
        sql = sql +` AND s.dept='${req.query.dept}'`
    }
    if(req.query.cname!=''){
        sql = sql +` AND c.cname='${req.query.cname}'`
    }
    sql = sql + ' ORDER BY s.sno'
    console.log(sql);
    selectSql(sql)
    .then((data) => {
        res.send(JSON.stringify(data));  
    })
})
app.get('/add',(req,res) => {
    let sql = `INSERT INTO s VALUES (${req.query.sno}, '${req.query.sname}', '${req.query.sex}', ${req.query.age}, '${req.query.dept}')`
    addSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
app.get('/score',(req,res) => {
    console.log(req.query);
    let sql = `INSERT INTO sc VALUES (${req.query.sno}, ${req.query.cno}, ${req.query.grade}, ${req.query.credit})`
    scoreSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
app.get('/edit',(req,res) => {
    console.log(req.query);
    let sql = `update s,sc set s.sname='${req.query.sname}',sc.grade=${req.query.grade},s.dept='${req.query.dept}' where s.sno=${req.query.sno} AND sc.sno=${req.query.sno} AND sc.cno=${req.query.cno}`
    console.log(sql);
    scoreSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
app.get('/update',(req,res) => {
    console.log(req.query);
    let sql = `update user set pwd=${req.query.newPwd} where hm='${req.query.name}'`
    console.log(sql);
    scoreSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})
app.get('/delete',(req,res) => {
    let sql = `delete from sc where grade=${req.query.grade};`
    console.log(sql);
    scoreSql(sql)
    .then((data) => {
        res.send(data)
    },(reject) => {
        res.send(reject)
    })
})

app.listen(9000)
console.log('服务器启动成功');